using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using DatabaseUnitTesting.Utilities;
using DatabaseUnitTesting.Utilities.StoredProcedure;

namespace DatabaseUnitTesting
{
    public class StoredProcedureUnitTester
    {
        private readonly SqlConnection _connection;
        private readonly SqlCommand _storedProcedure;
        private readonly string _procedureDatabase;
        private readonly DatabaseAdapter _databaseAdapter;
        private string _schemaName, _tableNameStub, _procedureTargetDatabase, _fileTargetDatabase;
        private string _procedureTargetFile, _fileTargetFile;
        private bool _outputOnFailure = false;

        public StoredProcedureUnitTester(SqlConnection connection, string procedureDatabase,
                                         string procedureName)
        {
            _connection = connection;
            _procedureDatabase = procedureDatabase;
            _databaseAdapter = new DatabaseAdapter(connection);
            _storedProcedure = new SqlCommand(procedureName, connection);
            _storedProcedure.CommandType = CommandType.StoredProcedure;
        }

        public bool OutputOnFailure
        {
            get { return _outputOnFailure; }
            set { _outputOnFailure = value; }
        }

        public void PrintOuputParameterValues()
        {
            foreach(SqlParameter p in _storedProcedure.Parameters)
                if(p.Direction == ParameterDirection.Output)
                    Console.WriteLine(p.ParameterName + " = " + p.Value);
        }

        public bool CompareToFile(string filename)
        {
            Database procedureResults = ResultSetParser.Parse(_storedProcedure);
            Database fileResults = XmlFileAdapter.Read(filename);

            if(!procedureResults.Equals(fileResults))
                return Failure(filename, procedureResults, fileResults);

            return true;
        }

        internal bool Failure(string dataFile, Database procedureResults, Database fileResultSets)
        {
            if(_outputOnFailure)
            {
                if(_procedureTargetFile != null || _procedureTargetDatabase != null)
                {
                    List<string> procedureSqlCommands =
                        SqlGenerator.GenerateScript(procedureResults, _schemaName, _tableNameStub);
                    if(_procedureTargetFile != null)
                        SqlFileAdapter.WriteCommandsToFile(procedureSqlCommands,
                                                           _procedureTargetFile);
                    if(_procedureTargetDatabase != null)
                        SqlExecuter.ExecuteScript(_connection, procedureSqlCommands,
                                                  _procedureTargetDatabase);
                }

                if(_fileTargetFile != null || _fileTargetDatabase != null)
                {
                    List<string> fileSqlCommands =
                        SqlGenerator.GenerateScript(fileResultSets, _schemaName, _tableNameStub);
                    if(_fileTargetFile != null)
                        SqlFileAdapter.WriteCommandsToFile(fileSqlCommands, _fileTargetFile);
                    if(_fileTargetDatabase != null)
                        SqlExecuter.ExecuteScript(_connection, fileSqlCommands, _fileTargetDatabase);
                }
            }
            return false;
        }

        public void OutputOnFailureTarget(string procedureTargetFile, string fileTargetFile,
                                          string procedureTargetDatabase, string fileTargetDatabase,
                                          string schemaName, string tableNameStub)
        {
            _tableNameStub = tableNameStub;
            _schemaName = schemaName;

            _procedureTargetFile = procedureTargetFile;
            _procedureTargetDatabase = procedureTargetDatabase;

            _fileTargetFile = fileTargetFile;
            _fileTargetDatabase = fileTargetDatabase;
        }

        public void OutputToFile(string filename)
        {
            _databaseAdapter.UseDatabase(_procedureDatabase);
            Database results = ResultSetParser.Parse(_storedProcedure);
            XmlFileAdapter.Write(filename, results);
        }

        public void SetInputParameter(string parameterName, object parameterValue)
        {
            if(_storedProcedure.Parameters.Contains(parameterName))
                _storedProcedure.Parameters[parameterName] =
                    new SqlParameter(parameterName, parameterValue);
            else
                _storedProcedure.Parameters.AddWithValue(parameterName, parameterValue);
        }

        public void SetOutputParameter(string parameterName, SqlDbType type, int size)
        {
            if(_storedProcedure.Parameters.Contains(parameterName))
                _storedProcedure.Parameters.RemoveAt(parameterName);

            _storedProcedure.Parameters.Add(parameterName, type);
            _storedProcedure.Parameters[parameterName].Direction = ParameterDirection.Output;
            if(size != 0)
                _storedProcedure.Parameters[parameterName].Size = size;
        }
    }
}